Step 1 - Text file with the new columns
In the zip file that you extracted in Step 2, open the text file, Raleigh_Police_Incidents_Columns_v01.txt, that has the formulas to create columns. The file is located in the DAX_Measures_Columns folder.
Step 2 -Load Data
With the Power BI Report file open, click on Home -> Get Data -> and under Common Data Sources, select Excel. Browse to the dataset folder and select the Excel file, Raleigh_Police_Incidents__NIBRS_2014-2020_v02.xlsx -> Select the worksheet and click load. Once the table is loaded, click on the table, Raleigh Police Incidents (NIBRS), on right-hand side of the window pane.
Step 3 - Create the new columns that will be needed
Open the text file, found in the folder DAX_Measures_Columns, the file is named Raleigh_Police_Incidents_Columns_v01.txt
After clicking on the table, Raleigh Police Incidents (NIBRS), click to Table Tools -> New Column and add all the columns in the text file.
The following example is how to create the file column, Weekday_Number.
Follow the same steps to create the remainder new columns.
Note: If you don't see the option to add a new column, be sure you are in the 'Table Tools' tab. Also, be sure to click on 'New Column' first, to avoid overwriting your previously created column(s).
Verify all columns were created successfully:
When finished, save your Power BI Report file.
Step 4: Format the Time_by_Hour column to 13:30 (hh:nn):
Step 5: Create the LastFresh_Local table
With Power BI Power BI Desktop still open, click the Home tab.
Click Get Data -> Blank Query
Go to Home -> Advanced Editor and paste the following syntax in the window with the following:
let
Source = #table(type table[LastRefresh=datetime], {{DateTime.LocalNow()}})
in
Source
Click Done and rename the query “LastRefresh_Local”
Click Close & Apply to save your changes.
When finished, save your project.
To load Excel data in Power BI:
Connect to Excel in Power BI Desktop
To create a blank table: